package com.deepclassifieds.daas.api.jdbc.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.deepclassifieds.daas.api.jdbc.name.TableName;
import com.deepclassifieds.daas.api.util.XMLGregorianCalendarHelper;
import com.deepclassifieds.daas.api.xsd.Column;
import com.deepclassifieds.daas.api.xsd.ColumnDef;
import com.deepclassifieds.daas.api.xsd.ColumnDefs;
import com.deepclassifieds.daas.api.xsd.Columns;
import com.deepclassifieds.daas.api.xsd.Datatype;
import com.deepclassifieds.daas.api.xsd.Record;
import com.deepclassifieds.daas.common.DualMap;
import com.deepclassifieds.daas.common.Reject;

public class DialectHSQLDBImpl implements Dialect {

	private static final Logger logger = LoggerFactory.getLogger(DialectHSQLDBImpl.class);

	private static final Locale locale = new Locale("hsqldb");
	
	private static final int DEFAULT_MAX_LENGTH = 255;
	
	private static final DualMap<Datatype,String> dualMap = new DualMap<Datatype,String>();
	static {
		dualMap.put(Datatype.BOOLEAN,  "BOOLEAN");
		dualMap.put(Datatype.BYTE,     "TINYINT");
		dualMap.put(Datatype.SHORT,    "SMALLINT");
		dualMap.put(Datatype.INTEGER,  "INTEGER");
		dualMap.put(Datatype.LONG,     "BIGINT");
		dualMap.put(Datatype.FLOAT,    "FLOAT");
		dualMap.put(Datatype.DOUBLE,   "DOUBLE");
		dualMap.put(Datatype.DECIMAL,  "DECIMAL");
		dualMap.put(Datatype.STRING,   "VARCHAR");
		dualMap.put(Datatype.DATETIME, "TIMESTAMP");
	}
	
	@Override
	public boolean isCaseSensitive() {
		return false;
	}

	@Override
	public Locale getLocale() {
		return locale;
	}

	@Override
	public String createTable(TableName tableName, ColumnDefs columnDefs) {
		Reject.ifNull(tableName);
		Reject.ifNull(columnDefs);
		int size = columnDefs.getColumnDef().size();
		StringBuilder sb = new StringBuilder(64 * size + 256);
		sb.append("CREATE TABLE ").append(tableName).append(" (\n");
		List<ColumnDef> uniqueColumnDefs = new ArrayList<ColumnDef>();
		for (ColumnDef columnDef : columnDefs.getColumnDef()) {
			appendColumnDDL(sb, columnDef);
			sb.append(",\n");
			if(columnDef.isUnique()) {
				uniqueColumnDefs.add(columnDef);
			}
		}
		sb.append("  ").append(DaasColumnName.DAAS_UID).append(" ").append(dualMap.getValue(Datatype.LONG)).append(" IDENTITY NOT NULL,\n");
		sb.append("  ").append(DaasColumnName.DAAS_UUID).append(" ").append(dualMap.getValue(Datatype.STRING)).append("(36) NOT NULL,\n");
		sb.append("  ").append(DaasColumnName.DAAS_CREATION_DATE).append(" ").append(dualMap.getValue(Datatype.DATETIME)).append(" NOT NULL,\n");
		sb.append("  ").append(DaasColumnName.DAAS_LAST_MODIFICATION_DATE).append(" ").append(dualMap.getValue(Datatype.DATETIME)).append(" NOT NULL,\n");
		sb.append("  ").append(DaasColumnName.DAAS_VERSION).append(" ").append(dualMap.getValue(Datatype.INTEGER)).append(" NOT NULL");
		for(ColumnDef uniqueColumnDef : uniqueColumnDefs) {
			sb.append(",\n");
			sb.append("CONSTRAINT ").append(" ").append(tableName.toString()).append('_').append(uniqueColumnDef.getName());
			sb.append(" UNIQUE (").append(uniqueColumnDef.getName()).append(")");
			
		}
		sb.append("\n)");
		return sb.toString();
	}
	
	@Override
	public String alterTableAddColumn(TableName tableName, ColumnDef column) {
		Reject.ifNull(tableName);
		Reject.ifNull(column);
		StringBuilder sb = new StringBuilder(256);
		sb.append("ALTER TABLE ").append(tableName).append(" ADD COLUMN ");
		appendColumnDDL(sb, column);
		return sb.toString();
	}

	/** Map<metadataTableName,columnName> */
	private final Map<String,String> idColumnMap = new ConcurrentHashMap<String,String>();
	
	@Override
	public String resolveIdentityColumnName(TableName tableName, Connection conn) throws SQLException {
    	String metadataTableName = tableName.toString().toUpperCase();
    	String idColumnName = idColumnMap.get(metadataTableName); 
    	if(idColumnName == null) {
        	DatabaseMetaData metadata = conn.getMetaData();
        	ResultSet primaryKeysResultSet = metadata.getPrimaryKeys(null, null, metadataTableName);
        	if (primaryKeysResultSet.next()) {
        		idColumnName = primaryKeysResultSet.getString("COLUMN_NAME");
        		logger.debug("Resolved identity column from JDBC METADATA, table='{}', column='{}'", metadataTableName, idColumnName);
        	}
        	primaryKeysResultSet.close();
        	idColumnMap.put(metadataTableName, idColumnName);
    	}
    	return idColumnName;
//    	ResultSet columnsResultSet = metadata.getColumns(null, null, table.getName().toUpperCase(), null);
//    	while (columnsResultSet.next()) {
//    		String COLUMN_NAME = columnsResultSet.getString("COLUMN_NAME");
//    		String TYPE_NAME = columnsResultSet.getString("TYPE_NAME");
//    		int COLUMN_SIZE = columnsResultSet.getInt("COLUMN_SIZE");
//    		int DATA_TYPE = columnsResultSet.getInt("DATA_TYPE");
//    		String IS_AUTOINCREMENT = columnsResultSet.getString("IS_AUTOINCREMENT");		    		
//    		System.out.println("COLUMN_NAME=[" + COLUMN_NAME + 
//    				"], TYPE_NAME=[" + TYPE_NAME + 
//    				"], COLUMN_SIZE=[" + COLUMN_SIZE + 
//    				"], DATA_TYPE=[" + DATA_TYPE +
//    				"], IS_AUTOINCREMENT=[" + IS_AUTOINCREMENT +
//    				"]");
//    	}
	}

	private static StringBuilder appendColumnDDL(StringBuilder sb, ColumnDef columnDef) {
		assert sb != null;
		assert columnDef != null;
		sb.append("  ").append(columnDef.getName()).append(" ");
		switch(columnDef.getDatatype()) {
//		case AUTO_GENERATED_IDENTITY: 
//			sb.append(" IDENTITY NOT NULL"); 
//			break;
//		case APPLICATION_IDENTITY: 
//			sb.append(" PRIMARY KEY NOT NULL"); 
//			break;
		case STRING: 
			sb.append(dualMap.getValue(columnDef.getDatatype())).append("(");
			sb.append(columnDef.getMaxLength() != null && columnDef.getMaxLength() > 0 ? columnDef.getMaxLength() : DEFAULT_MAX_LENGTH).append(")");
			sb.append(columnDef.isNotNull() ? " NOT NULL" : " NULL");
			break;
		default:     
			sb.append(dualMap.getValue(columnDef.getDatatype())); 
			sb.append(columnDef.isNotNull() ? " NOT NULL" : " NULL");
			break;
		}
//		sb.append(column.getIdentityType() != null ? " NOT NULL" : " NULL");
//		if(column.getIdentityType() != null) {
//			switch(column.getIdentityType()) {
//			case AUTO_GENERATED_IDENTITY: sb.append(" IDENTITY"); break;
//			case APPLICATION_IDENTITY: sb.append(" PRIMARY KEY"); break;
//			}
//		}
		return sb;
	}

	@Override
	public Record toRecord(ResultSet rs) throws SQLException {
		assert rs != null;
		ResultSetMetaData metaData = rs.getMetaData();
		Record record = new Record();
		Columns columns = new Columns();
		record.setColumns(columns);
		int columnCount = metaData.getColumnCount();
		for(int i = 0; i < columnCount; ++i) {
			int columnIndex = i + 1;
			String columnName = isCaseSensitive() ? 
					metaData.getColumnName(columnIndex) : metaData.getColumnName(columnIndex).toLowerCase(); 
//			String columnTypeName = metaData.getColumnTypeName(columnIndex);
//			boolean nullable = metaData.isNullable(columnIndex) == ResultSetMetaData.columnNullable;
			if(DaasColumnName.DAAS_UID.equalsIgnoreCase(columnName)) {
				long daas_uid = rs.getLong(columnIndex);
				record.setUid(daas_uid);
			} else if(DaasColumnName.DAAS_UUID.equalsIgnoreCase(columnName)) {
				String daas_uuid = rs.getString(columnIndex);
				record.setUuid(daas_uuid);
			} else if(DaasColumnName.DAAS_CREATION_DATE.equalsIgnoreCase(columnName)) {
				Timestamp daas_creation_date = rs.getTimestamp(columnIndex);
				record.setCreationDate(XMLGregorianCalendarHelper.toXMLGregorianCalendar(daas_creation_date));
			} else if(DaasColumnName.DAAS_LAST_MODIFICATION_DATE.equalsIgnoreCase(columnName)) {
				Timestamp daas_last_modification_date = rs.getTimestamp(columnIndex);
				record.setLastModificationDate(XMLGregorianCalendarHelper.toXMLGregorianCalendar(daas_last_modification_date));
			} else if(DaasColumnName.DAAS_VERSION.equalsIgnoreCase(columnName)) {
				int daas_version = rs.getInt(columnIndex);
				record.setVersion(daas_version);
			} else {
				String columnValue = rs.getString(columnIndex);
				Column column = new Column();
				column.setName(columnName);
				column.setNull(rs.wasNull());
				column.setValue(columnValue);
				columns.getColumn().add(column);
//				Datatype datatype = dualMap.getKey(columnTypeName.trim().toUpperCase());
//				ColumnDefsBuilder columnsBuilder = new ColumnDefsBuilder(columns);
//				switch(datatype) {
//				case STRING: 
//					int maxLength = metaData.getColumnDisplaySize(columnIndex);
//					columnsBuilder.createStringColumn(new Name(columnName), maxLength, columnValue);
//					break;
//				default:
//					columnsBuilder.createColumn(new Name(columnName), datatype, columnValue);
//					break;
//				}
			}
		}
		return record;
	}

}
